<?php
	class Phongtrongtheongay_model extends CI_Model 
	{
	    function LayTatCaPhongTrong_Giuong_Phong_KhachSan() 
		{
        	$post_per_page = 20;
	        $num = 0;
    	    if ($this->uri->segment(3))
        	    $num = $this->uri->segment(3);

	        $strSQL = "SELECT pt.*, lg.Ten AS TenLG, lp.Ten AS TenLP, ks.MaKS, ks.Ten AS TenKS
						FROM PhongTrongTheoNgay pt
						JOIN LoaiGiuong lg ON pt.LoaiGiuong = lg.MaLG						
						JOIN LoaiPhong lp ON pt.LoaiPhong = lp.MaLP
						JOIN KhachSan ks ON lp.KhachSan = ks.MaKS
						JOIN NhaCungCap ncc ON ks.NhaCungCap = ncc.MaNCC
						WHERE ncc.TinhTrangTaiKhoan = 1
						ORDER BY pt.Ngay DESC, ks.Ten ASC
						LIMIT $num, $post_per_page";
    	    $query = $this->db->query($strSQL);

        	$total_query = "SELECT pt.*, lg.Ten AS TenLG, lp.Ten AS TenLP, ks.MaKS, ks.Ten AS TenKS
							FROM PhongTrongTheoNgay pt
							JOIN LoaiGiuong lg ON pt.LoaiGiuong = lg.MaLG						
							JOIN LoaiPhong lp ON pt.LoaiPhong = lp.MaLP
							JOIN KhachSan ks ON lp.KhachSan = ks.MaKS
							JOIN NhaCungCap ncc ON ks.NhaCungCap = ncc.MaNCC
							WHERE ncc.TinhTrangTaiKhoan = 1";

	        $this->load->library('pagination');
    	    $config['base_url'] = base_url() . 'index.php/phongtrongtheongay/admin_index';
	        $config['total_rows'] = $this->db->query($total_query)->num_rows();
    	    $config['per_page'] = $post_per_page;
        	$this->pagination->initialize($config);

	        if ($query->num_rows() > 0) 
			{
    	        foreach ($query->result() as $row) 
				{
                	$data[] = $row;
	            }
    	        return $data;
        	}
	        return NULL;
    	}

	    function LayPhongTrong_Giuong_Phong_KhachSan($mapt) 
		{
        	$this->db->select('pt.*, lg.Ten AS TenLG, lp.Ten AS TenLP, ks.MaKS, ks.Ten AS TenKS');
	        $this->db->from('PhongTrongTheoNgay pt');
    	    $this->db->join('LoaiGiuong lg', 'pt.LoaiGiuong = lg.MaLG');
	        $this->db->join('LoaiPhong lp', 'pt.LoaiPhong = lp.MaLP');
    	    $this->db->join('KhachSan ks', 'lp.KhachSan = ks.MaKS');
        	$this->db->where('pt.MaPT', $mapt);
	        $query = $this->db->get();
    	    if ($query->num_rows() > 0) 
			{
            	foreach ($query->result() as $row) 
				{
                	$data[] = $row;
            	}
	            return $data;
    	    }
        	return NULL;
	    }

    	function Tim($key_ngay, $key_khachsan) 
		{
        	$post_per_page = 20;
	        $num = 0;
    	    if (!$key_ngay) 
			{
        	    if ($this->uri->segment(4))
            	    $num = $this->uri->segment(4);
        	}
	        else
    	    if ($this->uri->segment(5))
	            $num = $this->uri->segment(5);

    	    if ($key_khachsan == "Nhập Tên khách sạn")
        	    $key_khachsan = "";

	        if ($key_ngay == '1970-01-01')
    	        $where = "WHERE ks.Ten LIKE '%$key_khachsan%'";
        	else
            	$where = "WHERE pt.Ngay = '$key_ngay' AND ks.Ten LIKE '%$key_khachsan%'";

	        $strSQL = "SELECT pt.*, lg.Ten AS TenLG, lp.Ten AS TenLP, ks.MaKS, ks.Ten AS TenKS
						FROM PhongTrongTheoNgay pt
						JOIN LoaiGiuong lg ON pt.LoaiGiuong = lg.MaLG						
						JOIN LoaiPhong lp ON pt.LoaiPhong = lp.MaLP
						JOIN KhachSan ks ON lp.KhachSan = ks.MaKS
						JOIN NhaCungCap ncc ON ks.NhaCungCap = ncc.MaNCC
						$where
						AND ncc.TinhTrangTaiKhoan = 1
						ORDER BY pt.Ngay DESC, ks.Ten ASC
						LIMIT $num, $post_per_page";
    	    $query = $this->db->query($strSQL);

        	$total_query = "SELECT pt.*, lg.Ten AS TenLG, lp.Ten AS TenLP, ks.MaKS, ks.Ten AS TenKS
							FROM PhongTrongTheoNgay pt
							JOIN LoaiGiuong lg ON pt.LoaiGiuong = lg.MaLG						
							JOIN LoaiPhong lp ON pt.LoaiPhong = lp.MaLP
							JOIN KhachSan ks ON lp.KhachSan = ks.MaKS
							JOIN NhaCungCap ncc ON ks.NhaCungCap = ncc.MaNCC
							$where
							AND ncc.TinhTrangTaiKhoan = 1";

	        if ($key_khachsan == "")
    	        $key_khachsan = "Nhập Tên khách sạn";

        	$this->load->library('pagination');
	        if (!$key_ngay)
    	        $config['uri_segment'] = 4;
        	else
            	$config['uri_segment'] = 5;
	        $config['base_url'] = base_url() . 'index.php/phongtrongtheongay/Tim/' . $key_ngay . "/" . $key_khachsan;
    	    $config['total_rows'] = $this->db->query($total_query)->num_rows();
        	$config['per_page'] = $post_per_page;
	        $this->pagination->initialize($config);

    	    if ($query->num_rows() > 0) 
			{
            	foreach ($query->result() as $row) 
				{
                	$data[] = $row;
	            }
    	        return $data;
        	}
	        return NULL;
    	}
		
		function LayKhachSan_ajax($key)
		{
        	$this->db->select('ks.Ten');
	        $this->db->from('PhongTrongTheoNgay pt');
	        $this->db->join('LoaiPhong lp', 'pt.LoaiPhong = lp.MaLP');
    	    $this->db->join('KhachSan ks', 'lp.KhachSan = ks.MaKS');
			$this->db->join('NhaCungCap ncc', 'ks.NhaCungCap = ncc.MaNCC');
        	$this->db->like('ncc.TinhTrangTaiKhoan', 1, 'none');			
        	$this->db->like('ks.Ten', $key);
			$this->db->order_by('ks.Ten', 'asc');
			$this->db->distinct();
	        $query = $this->db->get();
    	    if ($query->num_rows() > 0) 
			{
            	foreach ($query->result() as $row) 
				{
                	$data[] = $row;
            	}
	            return $data;
    	    }
        	return NULL;			
		}

    	function LaySoPhong($ngay, $loaiphong, $loaigiuong) 
		{
        	$this->db->select('SoLuongPhongTrong');
	        $this->db->from('PhongTrongTheoNgay');
    	    $this->db->where('Ngay', $ngay);
        	$this->db->where('LoaiPhong', $loaiphong);
	        $this->db->where('LoaiGiuong', $loaigiuong);
    	    $query = $this->db->get();
        	if ($query->num_rows() > 0) 
			{
            	foreach ($query->result() as $row) 
				{
                	$phongtrong = $row->SoLuongPhongTrong;
            	}
            	return $phongtrong;
	        }
    	    return NULL;
	    }

    	function LayLoaiPhong($mapt) 
		{
        	$query = $this->db->get_where('PhongTrongTheoNgay', array('MaPT' => $mapt));

	        if ($query->num_rows() > 0) 
			{
            	foreach ($query->result() as $row) 
				{
                	$malp = $row->LoaiPhong;
            	}
	            return $malp;
    	    }
        	return NULL;
	    }

    	function LayPhongTrong_CuaMotLoaiPhong_TheoNgay($ngay, $malp) 
		{
        	$strSQL = "SELECT *
						FROM PhongTrongTheoNgay pt
						WHERE pt.Ngay >= '$ngay' AND pt.LoaiPhong = $malp";
	        $query = $this->db->query($strSQL);

    	    if ($query->num_rows() > 0) 
			{
            	foreach ($query->result() as $row) 
				{
                	$data[] = $row;
	            }
    	        return $data;
        	}
	        return NULL;
    	}

	    function CapNhatPhongTrong($data, $ngay, $loaiphong, $loaigiuong) 
		{
        	$this->db->update('PhongTrongTheoNgay', $data, array('Ngay' => $ngay, 'LoaiPhong' => $loaiphong, 'LoaiGiuong' => $loaigiuong));
	        return $this->db->affected_rows();
    	}

	    function CapNhat_GiaSauKhuyenMai($makm) 
		{
        	$this->load->model('thongtinkhuyenmai_model');
	        $khuyenmai = $this->thongtinkhuyenmai_model->LayKhuyenMai($makm);

        	if ($khuyenmai) 
			{
            	foreach ($khuyenmai as $row) 
				{
                	$giatri = $row->GiaTriKhuyenMai;
	                $ngaybatdau = $row->NgayBatDau;
    	            $ngayketthuc = $row->NgayKetThuc;
        	        $maks = $row->KhachSan;
            	}
	        }

    	    $this->load->model('loaiphong_model');
	        $loaiphong = $this->loaiphong_model->LayLoaiPhong_CuaMotKhachSan($maks);

			$today = date('Y-m-d');
			if($ngaybatdau > $today)
	    	    $ngay = $ngaybatdau;
			else
				$ngay = $today;
				
        	$result = 1;
	        while ($ngay <= $ngayketthuc) 
			{
            	if ($loaiphong) 
				{
                	foreach ($loaiphong as $row) 
					{
                    	$malp = $row->MaLP;
	                    $giatruockhuyenmai = $row->GiaTruocKhuyenMai;

    	                $giasaukhuyenmai = $giatruockhuyenmai - $giatruockhuyenmai * ($giatri / 100);
        	            $data = array('GiaSauKhuyenMai' => $giasaukhuyenmai);

            	        $result = $this->CapNhatGia($data, $ngay, $malp);

                	    if ($result < 0)
                    	    return $result;
                	}
            	}
	            $ngay = date('Y-m-d', strtotime($ngay . ' + 1 day'));
    	    }

        	return $result;
    	}

	    function CapNhatGia($data, $ngay, $loaiphong) 
		{
        	return $this->db->update('PhongTrongTheoNgay', $data, array('Ngay' => $ngay, 'LoaiPhong' => $loaiphong));
	    }

    	function Xoa($mapt) 
		{
        	return $this->db->delete('PhongTrongTheoNgay', array('MaPT' => $mapt));
	    }

    	function XoaNhieuDong($mapts) 
		{
        	if ($mapts) 
			{
            	foreach ($mapts as $mapt) 
				{
                	$result = $this->Xoa($mapt);
	                if ($result < 0)
	                    return $result;
    	        }
        	    return 1;
	        }
    	}

	    function Them($data) 
		{
        	$this->db->insert('PhongTrongTheoNgay', $data);
	        return $this->db->affected_rows();
    	}

	    function CapNhat($data, $mapt) 
		{
        	return $this->db->update('PhongTrongTheoNgay', $data, array('MaPT' => $mapt));
	    }

    	function update_records_phongtrongtheongay($startdate, $enddate, $loaiphong, $loaigiuong) {
        date_add($enddate, date_interval_create_from_date_string('0 days'));
            do {
            $start = date_format($startdate, 'Y-m-d');
            date_add($startdate, date_interval_create_from_date_string('1 days'));
            $strSQL = "SELECT SoLuongPhongTrong
						FROM PhongTrongTheoNgay pt
						WHERE pt.Ngay = ? AND pt.LoaiPhong = ? AND pt.LoaiGiuong = ?";
            $query = $this->db->query($strSQL, array($start, $loaiphong, $loaigiuong));

            if ($query->num_rows() > 0) {
                foreach ($query->result() as $row) {
                    $soluongphongtrong = $row->SoLuongPhongTrong;
                }
            }
            //else $soluongphongtrong = 0;
            $this->db->where('Ngay', $start);
            $this->db->where('LoaiPhong', $loaiphong);
            $this->db->where('LoaiGiuong', $loaigiuong);
            $this->db->set('SoLuongPhongTrong', $soluongphongtrong - 1);
            $this->db->update('PhongTrongTheoNgay');
            } while ($startdate != $enddate);
            return;
        }
	}
?>